package com.wmx.hb.fastmybatis.service.impl;

import com.gitee.fastmybatis.core.PageInfo;
import com.gitee.fastmybatis.core.query.Query;
import com.gitee.fastmybatis.core.query.Sort;
import com.gitee.fastmybatis.core.support.CommonService;
import com.wmx.hb.fastmybatis.mapper.PersonMapper;
import com.wmx.hb.fastmybatis.pojo.BasPersonInfo;
import com.wmx.hb.fastmybatis.service.PersonInfoService;
import org.apache.commons.lang3.time.DateUtils;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.text.ParseException;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 使用 fastmybatis 操作
 * 1、直接调用 Mapper 接口，自己不再实现通用接口 {@link CommonService}
 *
 * @author wangMaoXiong
 * @version 1.0
 * @date 2022/12/7 20:10
 */
@Service
public class PersonInfoServiceImpl implements PersonInfoService {

    @Resource
    private PersonMapper personMapper;

    /**
     * 分页查询
     *
     * @param pageIndex ：当前页码，必须大于等于1
     * @param pageSize  ：每页展示的条数，必须大于等于1
     * @return
     */
    @Override
    public PageInfo<BasPersonInfo> listPage(int pageIndex, int pageSize) {
        Query query = new Query();
        query.page(pageIndex, pageSize);
        PageInfo<BasPersonInfo> pageInfo = personMapper.page(query);
        return pageInfo;
    }

    /**
     * 查询指定部门下的员工
     *
     * @param deptNo ：部门Id
     * @return
     */
    @Override
    public List<Map<String, Object>> listMapByDeptNo(int deptNo) {
        Query query = new Query();
        query.eq("deptno", deptNo);
        query.orderby("ename", Sort.ASC);
        query.orderby("hiredate", Sort.DESC);

        List<String> columns = Arrays.asList("*");
        // SELECT * FROM `bas_person_info` t WHERE deptno = ? ORDER BY ename ASC,hiredate DESC
        List<Map<String, Object>> pageInfo = personMapper.listMap(columns, query);
        return pageInfo;
    }

    /**
     * com.gitee.fastmybatis.core.query.Query 查询方法测试
     * 查询方式	说明
     * eq	等于=
     * gt	大于>
     * lt	小于<
     * ge	大于等于>=
     * le	小于等于<=
     * notEq	不等于<>
     * like	模糊查询
     * in	in()查询
     * notIn	not in()查询
     * between	between查询
     * isNull	NULL值查询
     * notNull	IS NOT NULL
     * notEmpty	字段不为空，非NULL且有内容
     * isEmpty	字段为NULL或者为''
     * <p>
     * 一般来说分页的使用比较简单，通常是两个参数， pageIndex：当前页索引，pageSize：每页几条数据。
     * Query类使用**page(pageIdnex, pageSize)**方法来设置。 假如我们要查询第二页，每页10条数据: query.page(2, 10);
     * 如果要实现不规则分页，可以这样写：query.limit(3, 5) // 对应mysql：limit 3,5
     * <p>
     * 排序参数: orderby(String sortname, Sort sort),如 query.orderby("create_time",Sort.DESC).orderby("id",Sort.ASC);
     */
    @Override
    public void queryTest() throws ParseException {
        // SELECT t.empno , t.ename , t.job , t.mgr , t.hiredate , t.sal , t.comm , t.deptno FROM bas_person_info t WHERE ename = ? AND sal = ?
        Query query1 = new Query().eq("ename", "张三").eq("sal", 1600);
        List<BasPersonInfo> users1 = personMapper.list(query1);

        // WHERE deptno IN ( ? , ? , ? ) AND sal >= ? LIMIT ?,?
        Query query2 = new Query().in("deptno", Arrays.asList(2, 3, 4)).ge("sal", 1000).page(1, 20);
        List<BasPersonInfo> users2 = personMapper.list(query2);

        // WHERE hiredate > ? AND hiredate < ? ORDER BY hiredate DESC
        Date startDate = DateUtils.parseDate("1995-04-14", "yyyy-MM-dd");
        Date endDate = DateUtils.parseDate("2005-04-14", "yyyy-MM-dd");
        Query query3 = new Query().gt("hiredate", startDate).lt("hiredate", endDate).orderby("hiredate", Sort.DESC);
        long count3 = personMapper.getCount(query3);

        // WHERE (deptno IS NOT NULL) AND (ename like '%蚩尤%' and exists(select 1 from bas_person_info t2 where t2.empno = t.empno))
        // 默认的方法不能满足查询需求的话，可以使用自定sql的方式来编写查询条件
        // sql()方法不会处理sql注入问题，因此尽量少用.
        Query query4 = new Query().notNull("deptno");
        query4.sql("ename like '%蚩尤%' and exists(select 1 from bas_person_info t2 where t2.empno = t.empno)");
        long count4 = personMapper.getCount(query4);

        // WHERE deptno = ? OR ename = ?
        Query query5 = new Query().eq("deptno", 3).orEq("ename", "蚩尤后裔");
        long count5 = personMapper.getCount(query5);

        // WHERE ( deptno = ? OR comm BETWEEN ? AND ? ) AND ( hiredate > ? OR mgr = ? )
        Query query6 = new Query()
                .and(q -> q.eq("deptno", 3).orBetween("comm", 400, 1000))
                .and(q -> q.gt("hiredate", startDate).orEq("mgr", "salesman"));
        long count6 = personMapper.getCount(query6);

        // WHERE ( deptno = ? AND comm BETWEEN ? AND ? ) OR ( hiredate <= ? AND (mgr IS NOT NULL AND mgr <> '' ) )
        Query query7 = new Query()
                .and(q -> q.eq("deptno", 3).between("comm", 400, 1000))
                .or(q -> q.le("hiredate", endDate).notEmpty("mgr"));
        long count7 = personMapper.getCount(query7);
    }

    /**
     * 根据员工名称模糊查询
     *
     * @param ename
     * @return
     */
    @Override
    public List<BasPersonInfo> loadByName(String ename) {
        List<BasPersonInfo> personInfos = personMapper.loadByName(ename);
        return personInfos;
    }

    /**
     * 新增保存，忽略null字段
     *
     * @param basPersonInfo
     * @return
     */
    @Override
    public void save(BasPersonInfo basPersonInfo) {
        personMapper.saveIgnoreNull(basPersonInfo);
    }

    /**
     * 更新，更新所有字段
     *
     * @param basPersonInfo
     */
    @Override
    public void update(BasPersonInfo basPersonInfo) {
        personMapper.update(basPersonInfo);
    }

    /**
     * 根据id删除，在有逻辑删除字段的情况下，做UPDATE操作。
     *
     * @param id
     */
    @Override
    public void deleteById(Integer id) {
        personMapper.deleteById(id);
    }

}

